Many enterprise data processing systems rely on multi-node database servers to store and manage data. Such enterprise data processing systems typically follow a multi-tier model that has a multi-node database server in the first tier, and one or more computers in the middle tier and outer tiers.
FIG. 1 depicts multi-node database server mds11, which is implemented on multi-tier architecture 10. A server, such as multi-node database server mds11, is a combination of integrated software components and an allocation of computational resources, such as memory, a node, and processes on the node for executing the integrated software components on a processor, the combination of the software and computational resources being dedicated to performing a particular function on behalf of one or more clients. Resources from multiple nodes in a multi-node computer system can be allocated to run a particular server's software. A particular combination of the software on a node and the allocation of the resources from the node is a server that is referred to herein as a server instance or instance. Thus, a multi-node server comprises multiple server instances that can run on multiple nodes. Several instances of a multi-node server can even run on the same node.
A database server governs and facilitates access to a particular database, processing requests by clients to access the database. A multi-node database server, such as multi-node database server mds11, comprises multiple “database instances”, each database instance running on a node. Multi-node database server mds11 governs access to database db11. A multi-node database server can govern and facilitate access to one or more databases.
The middle-tier of multi-tier architecture 10 includes middle-tier computer cmp11 and the outer-tier includes user computer cmp12. User computer cmp12 executes browser br11, which interacts with an end-user. The end-user's interaction with browser br11 causes the browser to transmit requests over a network, such as the Internet, to middle-tier computer cmp11. The request causes a process on middle-tier computer cmp11, client cl11, to execute application appl11. Execution of application appl11 by the client cl11 causes client cl11 to connect to multi-node database server mds11. For example, application appl11 may be an order entry application that is configured to receive order requests from browser br11. Data for the order entry application is stored in db11. To process the requests, execution of application appl11 by client cl11 causes client cl11 to connect to database db11. Once connected, client cl11 issues database statements to retrieve and manipulate data stored in database db11.
The tier that directly connects to a server, relative to other tiers in a multi-tier architecture, is referred to herein as containing the client of the server. Thus, client process cl11 is referred to herein as the client of multi-node database server mds11.
An application, as the term is used herein, is a unit of software that is configured to interact with and use the functions of a server. In general, applications are comprised of integrated functions and software modules (e.g. programs comprised of machine executable code or interpretable code, dynamically linked libraries) that perform a set of related functions.
An application, such application appl11, interacts with a multi-node database server mds11 via client-side interface component intcomp11. Execution of application appl11 causes client cl11 to execute client-side interface component intcomp11 to interact with multi-node database server mds11. Application appl11 includes invocations of routines (e.g. functions, procedures, object methods, remote procedures) of client-side interface component intcomp11. Applications are typically developed by vendors and development teams different from those that develop servers and interfaces to servers, such as multi-node database server mds11 and client-side interface component intcomp11.
In order for a client to interact with multi-node database server mds11, a session is established for the client on a database instance of multi-node database server mds11. A session, such as a database session, is a particular connection established for a client to a server, such as a database instance, through which the client issues a series of requests (e.g., requests for execution of database statements).
For each database session established on a database instance, session state is maintained for the session. Session state includes the data stored for a database session for the duration of the database session. Such data includes, for example, the identity of the client for which the session is established, and temporary variable values generated by processes and database components executing software within the database session. A database component is a set of software modules that provide specialized and related functions for a database server, and shall be described later in greater detail. An example of a database component is a Java execution engine.
The beginning and end of a session demarcates a unit of work. Often, the beginning of a database session corresponds to an end-user establishing an interactive session with an application via, for example, a browser, and ends when the end-user logs off. Thus, the beginning and ending of the database session depend on application logic and end-user action, and may not be controlled by a server on which the session is established.
Client-Side Interface Components
Client-side interface components, such as client-side interface component intcomp11, are software components that reside and are executed on the same computer of a client of a server, and that are configured to provide an interface between the client and the server. The client-side interface component intcomp11 is configured for performing the detailed operations needed to interface with multi-node database server mds11. For example, application appl11 invokes a function of client-side interface component intcomp11 to establish a connection to multi-node database server mds11. The client-side interface component then handles the details of a connection on a particular instance of multi-node database server mds11. To make requests of multi-node database server mds11, such as a request for execution of a query, application appl11 is configured to invoke functions of client-side interface component intcomp11, which then transmits a request for the same to the node and database instance on which the session is established.
Client-side interface component intcomp11 may generate and/or access state that is hidden from other software modules, that is, is not or may not be referenced and accessed by other software modules, and in particular, by application appl11. Such state is referred to as being internal or private to client-side interface component intcomp11.
For example, to create a database session on a multi-node database server mds11, application appl11 invokes a routine of client-side interface component intcomp11. The client-side interface component establishes a database session on a particular database instance within multi-node database server mds11, and stores details about the database session within internal data structures or objects. Such data structures and objects define, for example, the session established for an application, and specify such values as an identity of a session on a database instance, the name of the database instance, and a network address and port number for the connection to the database instance.
Such details of the session are not returned to application appl11, nor may application appl11 access the details. Instead, what is provided to application appl11 is an “external identifier” for the session, such as a value that internal data of client-side interface component intcomp11 maps to the session, or a reference to an object generated by client-side interface component intcomp11 to store some details of the session in private attributes of the object that are inaccessible to application appl11. In this way, application appl11 does not “know” of the specific details of the session that has been established for it; however, application appl11 has the information needed to be able to identify to client-side interface component intcomp11 the particular session that has been established for application appl11.
Execution of Database Statements
Once a database session is established for an application, the application may request that a database server execute a query by invoking a function of a client-side interface component. Processing a request to execute a database statement goes through phases, each phase corresponding to particular types of operation. The phases include (1) creating a cursor, (2) parsing the query and binding its variables, (3) computing the query, (4) fetching rows to return for the query, and (5) closing the cursor. These phases are described in greater detail in Oracle8 Server Concepts, Release 8.0, Volume 3 (the contents of which incorporated herein by reference).
A cursor is an area of memory used to store information about a parsed query and other information related to the execution of the query. A cursor is part of a session's state. The rows computed for a query are referred to as the result set of the query or of the cursor created for the query.
A cursor may have various cursor states. Once the cursor is created, it is referred to being as being in the “opened state”, or as being opened, until it is in the “closed state”, or is closed. The cursor becomes closed under a variety of circumstances. For example, a cursor is closed once the entire result set for the query is computed, or once an application explicitly closes a cursor by issuing a command via the client-side interface component to close the cursor.
When the processing of a query is at a particular phase, its cursor may be referred to as being in a state corresponding to the phase. For example, when a cursor is in the phase for fetching rows, it may be referred to as being in the fetch state. When a cursor is in the phase for parsing a query and binding its variables, the cursor may be referred to as being in the parsing and binding state.
When a cursor is closed, it may be retained by a database server so that the cursor may be re-used in case the application again requests execution of the same query within the current database session. Retaining cursors in this way avoids having to repeat for an identical query the operations of creating a cursor and parsing the database statement and binding its variables, which can be expensive operations.
Application Fetching and Performance Considerations
While in the fetch state, the result set of a cursor is fetched by the client-side interface component, which furnishes the fetched rows to the application. The application may request rows from the result set one row at a time. In response, the client-side interface component fetches a row from the database server, by transmitting a fetch request for a row from the result set.
Each fetch request by a client-side interface component for a row entails transmission of the request across a network to the database server and transmission of the requested rows to the client-side interface component. To reduce such network traffic, “application fetching” is used.
Under application fetching, an application requests an application-specified number of rows (application-specified fetch quantity). The fetch request, which is referred to herein as an application fetch request, is transmitted by the application to the client-side interface component, which requests that number of rows from the database instance. The client-side interface component returns the requested number of rows to the application, which stores the rows in an “application fetch buffer”, a memory structure managed by the application. When the application requires more rows, it may generate additional application fetch requests, until all the rows of the result set are fetched from the database instance, thus completing the fetch phase of the cursor.
The size of an application fetch quantity affects performance and the use of memory on the client's computer. Bigger application-specified fetch quantities reduce network traffic by reducing the number of fetch requests needed to complete the fetch phase but require more memory on the client computer to store larger batches of fetched rows.
Smaller fetch quantities, on the other hand, require less memory on the client's computer but increase network traffic by increasing the number fetch requests needed to complete the fetch phase. Furthermore, cursors remain in the fetch state longer because fetching the entire result set for a cursor, which is being retrieved in smaller fetch quantities, takes relatively longer.
There are adverse consequences to cursors remaining in the fetch state longer. Some types of operations take longer to complete because the operations cannot be completed until all the cursors have left the fetch state. For example, migration of a session between database instances, as described in Transparent Session Migration Across Servers, cannot be completed until a session's cursor's are closed.
Another adverse consequence is that additional resources are consumed on the database server. These additional resources include resources that are directly consumed by cursors, such as memory for cursor state and memory for cached data blocks associated with any tables from which data is fetched for a cursor, and resources that are indirectly consumed, such as storage used for storing older undo information, which might be needed to return rows for a query based on an older snapshot (i.e. the state of a database at a previous point in time). This undo information is typically stored on disk, and can quickly increase in size. Keeping cursors open longer increases the need to keep such old undo information around, thereby consuming more disk storage.
Unfortunately, many applications are not developed to establish application-specified fetch quantities in a way that accounts for performance and resource allocation factors. Furthermore, even if attempts are made to develop applications in this way, applications may not be able to access information needed to optimally determine fetch quantities.
Based on the foregoing, it is clearly desirable to provide an improved approach for determining the number of rows to fetch in a fetch request.